package cn.com.jsepc.rep.subject_pool;

import cn.com.jsepc.rep.comm.Common;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.List;
import java.util.Vector;
import javax.servlet.ServletException;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import cn.com.jsepc.rep.comm.ConstUtil;
import cn.com.jsepc.rep.vo.subject_poolVO;
import cn.com.jsepc.rep.vo.subject_repVO;
import com.jarp.db.DAO;

public class subject_poolGrid {

    // 加载表格的数据
    public String getsubpGrid(String start, String limit, String KeyWord_bm,
            String KeyWord_mc, String nodeid) {
        String res = "";
        DAO dao = new DAO();
        try {
            List<subject_poolVO> lstResults = new ArrayList<subject_poolVO>();
            int iStart = Integer.parseInt(start);
            int iPageSize = Integer.parseInt(limit);
            int iCurPage = iStart / iPageSize + 1;
            StringBuffer sqlN = new StringBuffer();
            sqlN.append("select * from TB_SUBJECT_POOL where 1=1");
            // 点击树的情况
            if (nodeid != null && !nodeid.equals("")) {
                if (nodeid.equals("0")) {
                    if (KeyWord_bm != null && !KeyWord_bm.equals("")) {
                        sqlN.append(" and subCode like '%" + KeyWord_bm.toUpperCase() + "%'");
                    }
                    if (KeyWord_mc != null && !KeyWord_mc.equals("") && !KeyWord_mc.equals("")) {
                        sqlN.append(" and subName like '%" + KeyWord_mc + "%'");
                    }
                } else {
                    if (KeyWord_bm != null && !KeyWord_bm.equals("")) {
                        sqlN.append(" and subCode  like '%" + KeyWord_bm.toUpperCase() + "%'");
                    }
                    if (KeyWord_mc != null && !KeyWord_mc.equals("") && !KeyWord_mc.equals("")) {
                        sqlN.append(" and subName like '%" + KeyWord_mc + "%'");
                    }
                    if (nodeid != null && !nodeid.equals("")) {
                        sqlN.append(" and subPID = " + nodeid);
                    }
                }
            } else {
                if (KeyWord_bm != null && !KeyWord_bm.equals("")) {
                    sqlN.append(" and subCode  like '%"+ KeyWord_bm.toUpperCase() + "%'");
                }
                if (KeyWord_mc != null && !KeyWord_mc.equals("")
                        && !KeyWord_mc.equals("")) {
                    sqlN.append(" and subName like '%" + KeyWord_mc + "%'");
                }
            }
            Vector v = dao.getDataSet(ConstUtil.DBNAME, sqlN.toString(), iCurPage, iPageSize);
            if (v != null) {
                for (int i = 0; i < v.size(); i++) {
                    Hashtable hashRows = (Hashtable) v.get(i);
                    subject_poolVO subject_poolVo = new subject_poolVO();
                    subject_poolVo.setSubID((String) hashRows.get("SUBID"));
                    subject_poolVo.setSubCode((String) hashRows.get("SUBCODE"));
                    subject_poolVo.setSubName((String) hashRows.get("SUBNAME"));
                    subject_poolVo.setSubDesc((String) hashRows.get("SUBDESC"));
                    subject_poolVo.setSubPID((String) hashRows.get("SUBPID"));
                    subject_poolVo.setSubLevel((String) hashRows.get("SUBLEVEL"));
                    lstResults.add(subject_poolVo);
                }
            }
            JSONArray json = new JSONArray();
            StringBuffer str1 = new StringBuffer();
            str1.append("select count(1) CNT from (");
            str1.append(sqlN);
            str1.append(" ) z ");
            String totalCount = dao.getDataValue(ConstUtil.DBNAME, str1.toString());
            json = JSONArray.fromObject(lstResults);
            res = "{totalCount:" + totalCount + ",data:" + json.toString() + "}";
        } catch (Exception ex) {
            ex.printStackTrace();
            System.out.println("NewProjectTree getXmDl Exception.");
        }
        return res;
    }

    // 添加主题池
    public synchronized String subject_poolAdd(String itemJsonInfo)
            throws ServletException {
        String res = "";
        try {
            JSONObject jsonObject = new JSONObject(itemJsonInfo);
            DAO dao = new DAO();
            subject_poolVO subject_poolVo = new subject_poolVO();
            String subCode0 = jsonObject.getString("subCode").toUpperCase();
            String subName0 = jsonObject.getString("subName");
            String sql0 = "select * from TB_SUBJECT_POOL where subCode = '" + subCode0 + "' or  subName= '" + subName0 + "'";
            Vector v0 = dao.getDataSet(ConstUtil.DBNAME, sql0);
            if (v0 != null) {
                res = "false";
            } else {
                subject_poolVo.setSubCode(jsonObject.getString("subCode").toUpperCase());
                subject_poolVo.setSubName(jsonObject.getString("subName"));
                subject_poolVo.setSubDesc(jsonObject.getString("subDesc"));
                subject_poolVo.setSubPID(jsonObject.getString("subPID"));
                subject_poolVo.setSubLevel(jsonObject.getString("subLevel"));
                String subID0 = new Common().getSeqStr("seq_subject_pool");
                String sql = "insert into  TB_SUBJECT_POOL(" + "subID ,"
                        + "subCode," + "subName," + "subDesc," + "subPID,"
                        + "subLevel) values(" + subID0 + ",'"
                        + subject_poolVo.getSubCode() + "','"
                        + subject_poolVo.getSubName() + "','"
                        + subject_poolVo.getSubDesc() + "',"
                        + subject_poolVo.getSubPID() + ","
                        + subject_poolVo.getSubLevel() + ")";
                int bRet = dao.execCommand("Jarp", sql);
                if (bRet == 1) {
                    System.out.println("添加成功");
                    res = "true";
                } else {
                    System.out.println("添加失败");
                    res = "error";
                }
            }
        } catch (RuntimeException e) {
            e.printStackTrace();
        }
        return res;
    }

    // 修改主题池
    public synchronized String subject_poolUpdate(String itemJsonInfo)
            throws ServletException {
        String res = "";
        try {
            JSONObject jsonObject = new JSONObject(itemJsonInfo);
            DAO dao = new DAO();
            subject_poolVO subject_poolVo = new subject_poolVO();
            String subID = jsonObject.getString("subID");
            String subCode = jsonObject.getString("subCode").toUpperCase();
            String subName = jsonObject.getString("subName");
            String sql0 = "select * from TB_SUBJECT_POOL  where subID != "
                    + subID + " and (subCode= '" + subCode + "' or subName= '"
                    + subName + "')";
            Vector v0 = dao.getDataSet(ConstUtil.DBNAME, sql0);
            if (v0 != null) {
                res = "false";
            } else {
                subject_poolVo.setSubID(jsonObject.getString("subID"));
                subject_poolVo.setSubCode(jsonObject.getString("subCode").toUpperCase());
                subject_poolVo.setSubName(jsonObject.getString("subName"));
                subject_poolVo.setSubDesc(jsonObject.getString("subDesc"));
                subject_poolVo.setSubPID(jsonObject.getString("subPID"));
                subject_poolVo.setSubLevel(jsonObject.getString("subLevel"));

                String subID1 = subject_poolVo.getSubID();
                String subCode1 = subject_poolVo.getSubCode();
                String subName1 = subject_poolVo.getSubName();
                String subDesc1 = subject_poolVo.getSubDesc();
                String subPID1 = subject_poolVo.getSubPID();
                String subLevel1 = subject_poolVo.getSubLevel();
                String sql = "update TB_SUBJECT_POOL set subCode='" + subCode1
                        + "',subName='" + subName1 + "',subDesc='" + subDesc1
                        + "',subPID=" + subPID1 + ",subLevel=" + subLevel1
                        + " where subID=" + subID1;
                int bRet = dao.execCommand("Jarp", sql);
                if (bRet == 1) {
                    System.out.println("修改成功");
                    res = "true";
                } else {
                    System.out.println("修改失败");
                    res = "error";
                }
            }
        } catch (RuntimeException e) {
            e.printStackTrace();
        }
        return res;
    }

    // 删除主题池
    public synchronized String subject_poolDelete(String subID)
            throws ServletException {
        String res = "";
        DAO dao = new DAO();
        try {
            String sql0 = "select count(1) CC from TB_SUBJECT_POOL where subpID ="+ subID;
            String cc0 = dao.getDataValue(ConstUtil.DBNAME, sql0);
            if (cc0 != null && Integer.parseInt(cc0) > 0) {
                res = "havesub";
            } else {
                String sql1 = "select count(1) CC from TB_SUBJECT_REP where subjectID =" + subID;
                String cc = dao.getDataValue(ConstUtil.DBNAME, sql1);
                if (cc != null && Integer.parseInt(cc) > 0) {
                    res = "haverep";
                } else {
                    String sql = "delete from TB_SUBJECT_POOL where subID =" + subID + "";
                    int bRet = dao.execCommand("Jarp", sql);
                    if (bRet == 1) {
                        System.out.println("删除成功");
                        res = "true";
                    } else {
                        System.out.println("删除失败");
                        res = "false";
                    }
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            System.out.println("NewProjectTree getXmDl Exception.");
        }
        return res;
    }

    //展示末节点
    public String getSubId(String nodeid) {
        String res = "";
        DAO dao = new DAO();
        List<subject_poolVO> lstRes = new ArrayList<subject_poolVO>();
        try {
            String sqlZ = "select * from TB_SUBJECT_POOL where subID= " + nodeid;
            Vector v = dao.getDataSet(ConstUtil.DBNAME, sqlZ);
            if (v != null) {
                for (int i = 0; i < v.size(); i++) {
                    Hashtable hashRows = (Hashtable) v.get(i);
                    subject_poolVO subject_poolVo = new subject_poolVO();
                    subject_poolVo.setSubID((String) hashRows.get("SUBID"));
                    subject_poolVo.setSubCode((String) hashRows.get("SUBCODE"));
                    subject_poolVo.setSubName((String) hashRows.get("SUBNAME"));
                    subject_poolVo.setSubDesc((String) hashRows.get("SUBDESC"));
                    subject_poolVo.setSubPID((String) hashRows.get("SUBPID"));
                    subject_poolVo.setSubLevel((String) hashRows.get("SUBLEVEL"));
                    lstRes.add(subject_poolVo);
                }
            }
            JSONArray jsonZ = new JSONArray();
            jsonZ = JSONArray.fromObject(lstRes);
            res = "{totalCount:" + 1 + ",data:" + jsonZ.toString()+ "}";
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return res;
    }

    //加载主题池指标关系的数据
    public String getsubrepGrid(String start, String limit, String subID) {
        String res = "";
        DAO dao = new DAO();
        try {
            List<subject_repVO> lstResults = new ArrayList<subject_repVO>();
            int iStart = Integer.parseInt(start);
            int iPageSize = Integer.parseInt(limit);
            int iCurPage = iStart / iPageSize + 1;
            StringBuffer sqlN = new StringBuffer();
            sqlN.append("select * from ( select * from TB_SUBJECT_REP where subjectID=" + subID);
            sqlN.append(" ) a  left join tb_report b on b.repID=a.repID ");
            sqlN.append(" left join tb_subject_pool c on c.subID=a.subjectID ");
            sqlN.append(" left join TB_CATALOG d on d.cataid=b.cataid ");

            Vector v = dao.getDataSet(ConstUtil.DBNAME, sqlN.toString(), iCurPage, iPageSize);
            if (v != null) {
                for (int i = 0; i < v.size(); i++) {
                    Hashtable hashRows = (Hashtable) v.get(i);
                    subject_repVO subject_repVo = new subject_repVO();
                    subject_repVo.setRefID(Integer.parseInt((String) hashRows.get("REFID")));
                    subject_repVo.setSubjectID(Integer.parseInt((String) hashRows.get("SUBJECTID")));
                    subject_repVo.setSubName((String) hashRows.get("SUBNAME"));
                    subject_repVo.setRepname((String) hashRows.get("REPNAME"));
                    lstResults.add(subject_repVo);
                }
            }
            JSONArray json = new JSONArray();
            StringBuffer str1 = new StringBuffer();
            str1.append("select count(1) CNT from TB_SUBJECT_REP where subjectID=" + subID);
            String totalCount = dao.getDataValue(ConstUtil.DBNAME, str1.toString());
            json = JSONArray.fromObject(lstResults);
            res = "{totalCount:" + totalCount + ",data:" + json.toString() + "}";
        } catch (Exception ex) {
            ex.printStackTrace();
            System.out.println("NewProjectTree getXmDl Exception.");
        }
        return res;
    }

    //删除主题池关联的指标
    public synchronized String SubjectRepDelete(String array) throws ServletException {
        String re = "";
        DAO dao = new DAO();
        try {
            String[] refid = array.split(",");
            ArrayList<String> sqls = new ArrayList<String>();
            for (int i = 0; i < refid.length; i++) {
                String sql = "delete from TB_SUBJECT_REP where refID =" + refid[i];
                sqls.add(sql);
            }
            if (sqls.size() > 0) {
                if (dao.execCommand(ConstUtil.DBNAMEODS, sqls)) {
                    re = "true";
                    System.out.println("删除成功");
                } else {
                    re = "false";
                    System.out.println("删除失败");
                }
            } else {
                re = "false";
                System.out.println("no rowdata modify");
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            System.out.println("NewProjectTree getXmDl Exception.");
        }
        return re;
    }
}
